#! encoding: utf-8
from sqlalchemy import create_engine, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy import Column, String, Integer, Text
from sqlalchemy.orm import sessionmaker, relationship

class CRM(object):

    def __init__(self):
        self.remote_host = '10.119.67.124'
        self.remote_port  = '3306'
        self.db_username = 'crmPortalNew'
        self.db_passwd = 'crmPortalNew_2016'
        self.echo = True
        self.connect()

    def connect(self):
        st = 'mysql+mysqlconnector://{}:{}@{}:{}/crmPortalNew'.format(self.db_username,self.db_passwd,
                                                                      self.remote_host,self.remote_port)
        self.engine = create_engine(st, echo=self.echo).connect()

# 执行SQL
#cur = engine.execute('select distinct(businessAccessAccount)  from order_product_info;')
# 获取第一行数据
#one = cur.fetchone()

# 获取第n行数据
# cur.fetchmany(3)
# 获取所有数据
# cur.fetchall()


    def query_all_businessAccessAccounts(self):
        with self.engine.begin() as db_trans:
            cur = self.engine.execute('select distinct(businessAccessAccount)  from order_product_info;')
            # 获取第一行数据
            # one = cur.fetchone()
            #print(one)
            #print(one['businessAccessAccount'])
            # 获取第n行数据
            # cur.fetchmany(3)
            # 获取所有数据
            # cur.fetchall()
            Account = cur.fetchall()
            # db_trans.commit()
            return   [i['businessAccessAccount'] for i in Account]

    def query_businessAccessAccounts_by_userid(self, userid):
        with self.engine.begin() as db_trans:
            cur = self.engine.execute(" select distinct(t.businessAccessAccount) as businessAccessAccount from order_product_info t,\
                     order_info d where t.id = d.id and d.userId = '{}' ;".format(userid))
            # 获取第一行数据
            # one = cur.fetchone()
            #print(one)
            #print(one['businessAccessAccount'])
            # 获取第n行数据
            # cur.fetchmany(3)
            # 获取所有数据

            Account = cur.fetchall()

            return [i['businessAccessAccount'] for i in Account],Account

    def delete_by_userid(self, userid):
        self.db_trans = self.engine.begin()
        self.engine.execute("")

    def delete_all(self,):
        self.db_trans =  self.engine.begin()
        self.engine.execute("delete from t_order_work_flow_detail;")
        self.engine.execute("update order_work_flow set pre = null;")
        self.engine.execute("delete from order_work_flow ;")
        self.engine.execute("delete from order_product_info;")
        self.engine.execute("update order_info set old_order_info_id = null;")
        self.engine.execute("delete  from dxer_sales_info;")
        self.engine.execute("delete from t_pack_change;")
        self.engine.execute("delete from order_info;")
        self.engine.execute(
            "update t_user_id_info set used=0,customerName=null,customerPhone=null,handle=null,installAddress=null,ontSn=null,packageName=null;")
        self.engine.execute("delete  from t_user_id_recycle;")
        self.engine.execute("delete  from t_user_id_max;")
        self.engine.execute("delete  from t_user_id_change;")


if __name__ == '__main__':

    c = CRM()

    businessAccessAccounts = c.query_all_businessAccessAccounts()
    print (businessAccessAccounts)
    businessAccessAccounts,Account = c.query_businessAccessAccounts_by_userid('21fh800007')
    print(businessAccessAccounts,Account)
    c.commit()